package org.sunshine.miracle.dao;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.util.Assert;
import org.sunshine.miracle.model.VirtualFile;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

public class FilesManager extends JdbcDaoSupport {

    private static final Log LOGGER = LogFactory.getLog(FilesManager.class);

    private static final String SELECT_FILES_SQL =
            "select id, name, is_virtual, is_folder, absolute_path, parent_folder_id " +
                    "from virtual_file where parent_folder_id = ? order by name";

    private static final String SELECT_ROOT_FILES_SQL =
            "select id, name, is_virtual, is_folder, absolute_path, parent_folder_id " +
                    "from virtual_file where parent_folder_id is null order by name";

    private static final String GET_FILE_SQL = "select id, name, is_virtual, is_folder, absolute_path, parent_folder_id from virtual_file where id = ?";
    private static final String SELECT_CHILD_COUNT_SQL = "select count(*) from virtual_file where parent_folder_id = ?";
    private static final String INSERT_FILE_SQL = "insert into virtual_file(name, is_virtual, is_folder, absolute_path, parent_folder_id ) values (?,?,?,?,?)";
    private static final String UPDATE_FILE_SQL = "update virtual_file set name=?, is_virtual=?, is_folder=?, absolute_path=?, parent_folder_id=? where id = ?";
    private static final String DELETE_FILE_SQL = "delete from virtual_file where id = ?";


    public Long getChildrenCount(Long id) {
        long count = getJdbcTemplate().queryForLong(SELECT_CHILD_COUNT_SQL, new Object[]{id}, new int[]{Types.INTEGER});
        return count;
    }


    /**
     * return list of file for the givent parent folder.
     *
     * @param parentId id of parent folder. Set it null if you want to have root content.
     */
    public List<VirtualFile> getVirtualFiles(Long parentId) {
        if (parentId == null){
            List list = getJdbcTemplate().query(SELECT_ROOT_FILES_SQL, new VirtualFileRowMapper());
            return list;
        } else{

        List<VirtualFile> list = getJdbcTemplate().query(SELECT_FILES_SQL, new Object[]{parentId}, new int[]{Types.INTEGER}, new VirtualFileRowMapper());
        return list;
        }
    }

    public VirtualFile getVirtualFolder(Long id) {
        try {
            VirtualFile file = (VirtualFile) getJdbcTemplate().queryForObject(
                    GET_FILE_SQL, new Object[]{id}, new int[]{Types.INTEGER}, new VirtualFileRowMapper());
            return file;

        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public void saveVirtualFolder(final VirtualFile file) {
        if (file.getId() == null) {

            long newId = insertFolder(file);
            file.setId(newId);

        } else {

            int result = getJdbcTemplate().update(UPDATE_FILE_SQL,
                    new Object[]{file.getName(), file.isVirtual(), file.isFolder(), file.getAbsolutePath(), file.getParentId(), file.getId()},
                    new int[]{Types.VARCHAR, Types.BOOLEAN, Types.BOOLEAN, Types.VARCHAR, Types.INTEGER, Types.INTEGER});
            Assert.isTrue(1 == result, "Error when updating file.");
            LOGGER.debug("File updated.");
        }

    }

    private long insertFolder(final VirtualFile file) {
        long newId = (Long) DaoUtils.getTransactionTemplate().execute(new TransactionCallback() {
            public Object doInTransaction(TransactionStatus status) {

                int result = getJdbcTemplate().update(INSERT_FILE_SQL,
                        new Object[]{file.getName(), file.isVirtual(), file.isFolder(), file.getAbsolutePath(), file.getParentId()},
                        new int[]{Types.VARCHAR, Types.BOOLEAN, Types.BOOLEAN, Types.VARCHAR, Types.INTEGER});
                Assert.isTrue(1 == result, "Error when inserting file.");
                LOGGER.debug("Folder inserted.");

                Long id = getJdbcTemplate().queryForLong("call identity()");
                return id;

            }
        });
        return newId;
    }

    public void deleteFolder(Long id) {
        int result = getJdbcTemplate().update(DELETE_FILE_SQL,
                new Object[]{id}, new int[]{Types.INTEGER});
        Assert.isTrue(1 == result, "Error when deleting folder.");
        LOGGER.debug("Folder deleted.");
    }

    private static final String HAS_CHILD_SQL = "select count(*) from virtual_file where parent_folder_id=? and name=? and is_folder = ?";

    /** Check if folder with given id have file with such a name. */
    public boolean contains(Long parentId, String name, boolean isFolder) {
        long count = getJdbcTemplate().queryForLong(HAS_CHILD_SQL, new Object[]{parentId, name, isFolder}, new int[]{Types.INTEGER, Types.VARCHAR, Types.BOOLEAN});
        return count == 1;
    }

    private class VirtualFileRowMapper implements RowMapper {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            VirtualFile file = new VirtualFile();
            file.setId(rs.getLong(1));
            file.setName(rs.getString(2));
            file.setVirtual(rs.getBoolean(3));
            file.setFolder(rs.getBoolean(4));
            file.setAbsolutePath(rs.getString(5));
            String parentId = rs.getString(6);
            file.setParentId(parentId == null ? null : new Long(parentId));

            Long count = getChildrenCount(file.getId());
            file.setChildrenCount(count);


            return file;
        }
    }

}
